package com.mairuide.bill.utils;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import jxl.write.DateTime;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import com.mairuide.bill.DealFlow;

public class AnalysisTempBillUtils {
    /**
     * 解析XX银行EXCEL固定模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisXXXX(Workbook wb,String fileName) {


        return null;
    }

    /**
     * 解析中国银行EXCEL固定模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisCNBankEXCEL(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        String columns1[] = {"交易日期","交易账号","交易流水号","交易类型","交易时间","交易金额",
                "交易机构号","交易机构名","附言","付款人","付款账号","付款账户机构名","付款账户机构号",};
        String columns2[] = {"交易码","交易货币",
                "借贷标识","冲正标识","交易柜员","交易后余额","备注","用途","摘要","收款人",
                "收款账号","收款所属机构名","收款账户机构号"};
        List<Map<String,String>> list = null;
        String cellData = null;
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<Map<String,String>>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getLastRowNum()+2;

            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            boolean flag = true;
            Map<String,String> map = new LinkedHashMap<String,String>();
            for (int i = 0; i<rownum; i++) {
                row = sheet.getRow(i);
                if(row == null) {
                    list.add(map);
                    flag = true;
                    map = new LinkedHashMap<String,String>();
                    continue;
                }
                for (int j = 0;j<colnum;j++){
                    cellData = ExcelUtils.getCellValue(row.getCell(j));
                    if(cellData.equals("交易码")) {
                        flag = false;
                        break;
                    }else if(cellData.equals("交易日期")) {
                        break;
                    }
                    if(flag) {
                        map.put(columns1[j], cellData.trim());
                    }else {
                        map.put(columns2[j], cellData.trim());
                    }
                }
            }
        }
        List<DealFlow> dfs = new ArrayList<DealFlow>();
        if (list!=null&&list.size()>0){
            //遍历解析出来的list
            for (Map<String,String> map : list) {
                DealFlow deal = new DealFlow();

                String s = map.get("借贷标识");
                if ("C".equals(s)){//根据借贷标识判断交易主体是收款人还是付款人
                    deal.setSign(0);
                    deal.setAccountRival(map.get("付款人"));
                    deal.setAccountRivalNumber(map.get("付款账号"));
//                    deal.setAccountRivalCard(map.get("付款账号"));
                    deal.setTradingOrganizationNumber(map.get("付款账户机构名"));
                    deal.setAccountRivalBank(map.get("付款账户机构号"));
                    deal.setInstitutionNumber(map.get("收款账户机构号"));
                    deal.setAccountBody(map.get("收款人"));
                    deal.setAccountBodyCard(map.get("收款账号"));
                    deal.setAccountBodyBank(map.get("收款所属机构名"));
                }else if ("D".equals(s)){
                    deal.setSign(1);
                    deal.setAccountBody(map.get("付款人"));
                    deal.setAccountBodyCard(map.get("付款账号"));
                    deal.setAccountBodyBank(map.get("付款账户机构名"));
                    deal.setTradingOrganizationNumber(map.get("付款账户机构号"));
                    deal.setInstitutionNumber(map.get("收款账户机构号"));
                    deal.setAccountRival(map.get("收款人"));
                    deal.setAccountRivalNumber(map.get("收款账号"));
                    deal.setAccountRivalBank(map.get("收款所属机构名"));
                }
                deal.setAccountAddress(map.get("交易机构名"));
                deal.setAccountBodyNumber(map.get("交易账号"));
                //中国银行模板中有交易日期和交易时间，需要将两者合并一起再入库
                String date = map.get("交易日期");
                date = date.substring(0, 4)+"-"+date.substring(4, 6)+"-"+date.substring(6);
                String time = map.get("交易时间");
                String dateTime = date +" "+ time;

                Date date1 = DateUtils.getDate(dateTime);
                if(date1!=null)  deal.setTradeDate(new Timestamp(date1.getTime()));
                deal.setTellerNumber(map.get("交易柜员"));
                deal.setAccountMode(map.get("交易类型"));
                deal.setContentAbstract(map.get("摘要"));
                deal.setRemarks(map.get("备注"));
//                deal.setRemarks(map.get("附言"));

                deal.setSerialNumber(map.get("交易流水号"));

                deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                deal.setSurplusMoney(new BigDecimal(map.get("交易后余额")));

                //deal.setAccountRivalBank(map.get("付款账户机构号"));不在20个标题字段中

                deal.setCurrency(map.get("交易货币"));
                deal.setSerialNumber(map.get("交易码"));

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                dfs.add(deal);


            }

            System.out.println("保存单条记录成功");
        }
        //执行方法saveAll(dfs);


        return dfs;
    }

    /**
     * 解析平顶山1银行EXCEL固定模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisPdsbBank(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String[] columns = new String[20];
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getLastRowNum();
            //获取第一行
            //row = sheet.getRow(0);

            //先把公共信息放入map
            Map<String,String> map = new LinkedHashMap<String,String>();
            //用一个变量记录读取行数
            int i = 0;
            //读取表格头部信息，即主体账户信息

            row = sheet.getRow(1);
            if(row !=null){
                Row row1 = sheet.getRow(2);
                for (int j=2;j<=12;j++){
                    map.put(ExcelUtils.getCellValue(row.getCell(j)),ExcelUtils.getCellValue(row1.getCell(j)));
                }
            }

            //获取标题信息
            i=5;
            row = sheet.getRow(i);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            //标题数组,把标题写入数组
            for (int j=0;j<colnum;j++){
                String s = ExcelUtils.getCellValue(row.getCell(j));
                columns[j] = s;
            }
            //读取正文信息
            for ( i = i+1; i<=rownum; i++) {
                row = sheet.getRow(i);
                Map<String,String> map1 = new LinkedHashMap<String,String>();
                if(row !=null){
                    for (int j=0;j<colnum;j++){
                        cellData = ExcelUtils.getCellValue(row.getCell(j)).trim();
                        map1.put(columns[j], cellData);
                    }
                    map1.putAll(map);
                }else{
                    continue;
                }
                DealFlow deal = new DealFlow();

                deal.setAccountBody(map1.get("户名"));
                deal.setAccountBodyNumber(map1.get("账号"));
                deal.setAccountBodyCard(map1.get("借方账户"));
                String st = map1.get("借贷方");
                if ("借".equals(st)){
                    deal.setSign(1);
                }else {
                    deal.setSign(0);
                }
                SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");
                Date date = null;
                try {
                    date = sd.parse(map1.get("交易日期"));
                } catch (ParseException e) {
                    e.printStackTrace();
                }

                if(date!=null)  deal.setTradeDate(new Timestamp(date.getTime()));
                deal.setAccountRival(map.get("贷方户名"));
                deal.setAccountRivalNumber(map1.get("贷方账户"));
                if (!"".equals(map.get("交易金额"))&&map.get("交易金额")!=null)deal.setAccountMoney(new BigDecimal(map1.get("交易金额")));
                if (!"".equals(map.get("余额"))&&map.get("余额")!=null)deal.setSurplusMoney(new BigDecimal(map1.get("余额")));
                deal.setContentAbstract(map1.get("摘要"));
                deal.setAccountAddress(map1.get("机构名称"));
                deal.setSerialNumber(map1.get("交易流水号"));
                deal.setTellerNumber(map1.get("操作柜员"));
                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);
            }
        }

        return list;
    }

    /**
     * 解析平顶山2银行EXCEL固定模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisPdsb2Bank(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String[] columns = new String[20];
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getLastRowNum();
            //获取第一行
            //row = sheet.getRow(0);

            //先把公共信息放入map
            Map<String,String> map = new LinkedHashMap<String,String>();
            //用一个变量记录读取行数
            int i = 0;
            //读取表格头部信息，即主体账户信息

            row = sheet.getRow(1);
            if(row !=null){
                Row row1 = sheet.getRow(2);
                for (int j=0;j<=12;j++){
                    map.put(ExcelUtils.getCellValue(row.getCell(j)),ExcelUtils.getCellValue(row1.getCell(j)));
                }
            }

            //获取标题信息
            i=5;
            row = sheet.getRow(i);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            //标题数组,把标题写入数组
            for (int j=0;j<colnum;j++){
                String s = ExcelUtils.getCellValue(row.getCell(j));
                columns[j] = s;
            }
            //读取正文信息
            for ( i = i+1; i<=rownum; i++) {
                row = sheet.getRow(i);
                Map<String,String> map1 = new LinkedHashMap<String,String>();
                if(row !=null){
                    for (int j=0;j<colnum;j++){
                        cellData = ExcelUtils.getCellValue(row.getCell(j)).trim();
                        map1.put(columns[j], cellData);
                    }
                    map1.putAll(map);
                }else{
                    continue;
                }
                DealFlow deal = new DealFlow();

                deal.setAccountBody(map1.get("户名"));
                deal.setAccountBodyNumber(map1.get("账号"));
                deal.setAccountBodyCard(map1.get("借方账户"));
                String st = map1.get("借贷方");
                if ("借".equals(st)){
                    deal.setSign(1);
                }else {
                    deal.setSign(0);
                }
                SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");
                Date date = null;
                try {
                    date = sd.parse(map1.get("交易日期"));
                } catch (ParseException e) {
                    e.printStackTrace();
                }

                if(date!=null)  deal.setTradeDate(new Timestamp(date.getTime()));
                deal.setAccountRival(map1.get("贷方户名"));
                deal.setAccountRivalNumber(map1.get("贷方账户"));
                if (!"".equals(map1.get("交易金额"))&&map1.get("交易金额")!=null)deal.setAccountMoney(new BigDecimal(map1.get("交易金额")));
                if (!"".equals(map1.get("余额"))&&map1.get("余额")!=null)deal.setSurplusMoney(new BigDecimal(map1.get("余额")));
                deal.setContentAbstract(map1.get("摘要"));
                deal.setAccountAddress(map1.get("机构名称"));
                deal.setSerialNumber(map1.get("交易流水号"));
                deal.setTellerNumber(map1.get("操作柜员"));

                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }

                list.add(deal);
            }
        }

        return list;
    }

    /**
     * 解析平安银行EXCEL固定模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisPingAnBank(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String[] columns = new String[20];
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getLastRowNum();
            //获取第一行
            //row = sheet.getRow(0);

            //先把公共信息放入map
            Map<String,String> map = new HashMap<String,String>();
            //用一个变量记录读取行数
            int i = 0;
            //读取表格头部信息，即主体账户信息
            for (i = i+1;i<rownum;i++){
                row = sheet.getRow(i);
                if(i==5)break;
                if(row !=null){
                    for (int j=1;;j=j+4){
                        cellData = ExcelUtils.getCellValue(row.getCell(j));
                        if ("".equals(cellData)||cellData==null)break;
                        map.put(cellData, ExcelUtils.getCellValue(row.getCell(j+2)));
                    }
                }
            }
            //获取标题信息
            i=i+1;
            row = sheet.getRow(i);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            //标题数组,把标题写入数组
            for (int j=0;j<colnum-1;j++){
                String s = ExcelUtils.getCellValue(row.getCell(j+2));
                columns[j] = s;
            }
            //读取正文信息
            for ( i = i+1; i<rownum; i++) {
                row = sheet.getRow(i);
                Map<String,String> map1 = new HashMap<String,String>();
                if(row !=null){
                    for (int j=2;j<=colnum;j++){
                        cellData = ExcelUtils.getCellValue(row.getCell(j));
                        map1.put(columns[j-2], cellData);
                    }
                }else{
                    break;
                }

                DealFlow deal = new DealFlow();
                deal.setAccountBody(map.get(" 户名："));
                deal.setAccountBodyNumber(map.get("账号："));
                deal.setAccountBodyCard(map.get("卡号："));
                SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");
                Date date = null;
                try {
                    date = sd.parse(map1.get("交易日期"));
                } catch (ParseException e) {
                    e.printStackTrace();
                }

                if(date!=null)  deal.setTradeDate(new Timestamp(date.getTime()));
                deal.setAccountRival(map1.get("交易对方户名"));
                deal.setAccountRivalNumber(map1.get("交易对方账号"));
                String s1 = map1.get("借方发生额").replace(",","");
                if ("".equals(s1)|| s1==null ){
                    String ss = map1.get("贷方发生额").replace(",","");
                    deal.setAccountMoney(new BigDecimal(ss));
                    deal.setSign(0);
                }else{
                    deal.setAccountMoney(new BigDecimal(s1));
                    deal.setSign(1);
                }
                String s2 = map1.get("账户余额");
                if (!"".equals(s2)&&s2!=null)deal.setSurplusMoney(new BigDecimal(s2.replace(",","")));
                deal.setContentAbstract(map1.get("摘要"));
                deal.setAccountBodyBank(map.get("开户网点："));
                deal.setAccountRivalBank(map1.get("交易对方行名称"));
                deal.setAccountAddress(map1.get("交易网点"));
                deal.setCurrency(map.get("币种："));
                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);
            }
        }

        return list;
    }

    /**
     * 解析农商银行EXCEL固定模板
     * @param wb
     * @return
     */
    /*public List<DealFlow> analysisRuralCommercialBank(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String columns[] = new String[20];
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取交易详情sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一个表头信息
            row = sheet.getRow(2);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            //标题数组,把标题写入数组
            for (int j=0;j<colnum;j++){
                String s = ExcelUtils.getCellValue(row.getCell(j));
                columns[j] = s;
            }
            //读取正文
            for (int i = 3; i<rownum; i++) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(row !=null){
                    String s = ExcelUtils.getCellValue(row.getCell(0));
                    if (s.indexOf("：")>0)continue;
                    if (s.equals(columns[0]))continue;
                    for (int j=0;j<colnum;j++){
                        cellData = ExcelUtils.getCellValue(row.getCell(j));
                        map.put(columns[j], cellData);
                    }
                }else{
                    break;
                }
                DealFlow deal = new DealFlow();
                deal.setAccountBody(map.get("户  名"));
                deal.setAccountBodyNumber(map.get("账  号"));
                deal.setAccountAddress(map.get("交易机构名称"));
                deal.setContentAbstract(map.get("摘  要"));
                if (!"".equals(map.get("交易金额"))&&map.get("交易金额")!=null)deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                if (!"".equals(map.get("余  额"))&&map.get("余  额")!=null)deal.setSurplusMoney(new BigDecimal(map.get("余  额")));
                deal.setAccountRivalNumber(map.get("对方账号卡号"));
                String data =map.get("交易日期")+map.get("交易时间");
                SimpleDateFormat sd = new SimpleDateFormat("yyyyMMddHHssmm");
                Date date = null;
                try {
                    date = sd.parse(data);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                if(date!=null)  deal.setTradeDate(new Timestamp(date.getTime()));
                deal.setTellerNumber(map.get("交易柜员"));
                deal.setTradingOrganizationNumber(map.get("交易机构号"));

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);
            }
        }

        return list;
    }*/


    /**
     * 解析开封使用社银行EXCEL固定模板
     * @param wb
     * @return
     */
    /*public List<DealFlow> analysisHnnxBank(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String columns[] = {"交易机构号","交易机构名称","户  名","账  号","摘  要","交易金额","余  额","对方账号卡号","对方户名","交易日期","交易时间","交易柜员"};
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 2; i<rownum; i++) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(row !=null){
                    for (int j=0;j<colnum;j++){
                        cellData = ExcelUtils.getCellValue(row.getCell(j));
                        map.put(columns[j], cellData);
                    }
                }else{
                    break;
                }
                DealFlow deal = new DealFlow();
                deal.setAccountBody(map.get("户  名"));
                deal.setAccountAddress(map.get("交易机构名称"));
                deal.setAccountBodyNumber(map.get("账  号"));
                deal.setContentAbstract(map.get("摘  要"));
                if (!"".equals(map.get("交易金额"))&&map.get("交易金额")!=null)deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                if (!"".equals(map.get("余  额"))&&map.get("余  额")!=null)deal.setSurplusMoney(new BigDecimal(map.get("余  额")));
                deal.setAccountRivalNumber(map.get("对方账号卡号"));
                deal.setAccountRival(map.get("对方户名"));
                String data =map.get("交易日期")+map.get("交易时间");
                SimpleDateFormat sd = new SimpleDateFormat("yyyyMMddHHssmm");
                Date date = null;
                try {
                    date = sd.parse(data);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                if(date!=null)  deal.setTradeDate(new Timestamp(date.getTime()));
                deal.setSerialNumber(map.get("交易柜员"));
                deal.setTradingOrganizationNumber(map.get("交易机构号"));

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);
            }
        }

        return list;
    }*/

    /**
     * 解析浦发银行银行EXCEL固定模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisSPDBank(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String columns[] = {"柜员流水号","交易日期","交易时间","账号","客户号",
                "账号中文名","借贷标志","交易金额","","账户余额","摘要代码","客户账号","对方户名","对方账号","对方金融机构","对方机构号"};
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(1);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 2; i<rownum; i++) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if (ExcelUtils.isMergedRegion(sheet,i,0))continue;
                if(row !=null&&ExcelUtils.isBlankLine(row)){
                    for (int j=0;j<colnum;j++){
                        cellData = ExcelUtils.getCellValue(row.getCell(j));
                        map.put(columns[j], cellData.trim());
                    }
                }else{
                    continue;
                }
                DealFlow deal = new DealFlow();
                deal.setSerialNumber(map.get("柜员流水号"));
                String data = map.get("交易日期")+" "+map.get("交易时间");
                Date date = DateUtils.getDate(data);
                if(date!=null)  deal.setTradeDate(new Timestamp(date.getTime()));
                deal.setAccountBodyNumber(map.get("账号"));
                deal.setAccountBody(map.get("账号中文名"));
                String st = map.get("借贷标志");
                if ("0".equals(st)){
                    deal.setSign(1);
                }else {
                    deal.setSign(0);
                }
                if (!"".equals(map.get("交易金额"))&&map.get("交易金额")!=null)deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                if (!"".equals(map.get("账户余额"))&&map.get("账户余额")!=null)deal.setSurplusMoney(new BigDecimal(map.get("账户余额")));
                deal.setContentAbstract(map.get("摘要代码"));
                deal.setAccountBodyCard(map.get("客户账号"));
                deal.setAccountRival(map.get("对方户名"));
                deal.setAccountRivalNumber(map.get("对方账号"));
                deal.setTradingOrganizationNumber(map.get("对方金融机构"));

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);
            }
        }

        return list;
    }


    /**
     * 解析社旗农村信用社银行EXCEL固定模板
     * @param wb
     * @return
     */
    /*public List<DealFlow> analysisSheQiBank(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String columns[] = new String[20];
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一个表头信息
            row = sheet.getRow(1);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            //标题数组,把标题写入数组
            for (int j=0;j<colnum;j++){
                String s = ExcelUtils.getCellValue(row.getCell(j));
                columns[j] = s;
            }
            //读取正文
            for (int i = 2; i<rownum; i++) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(row !=null){
                    String s = ExcelUtils.getCellValue(row.getCell(0));
                    int index = s.indexOf("：");
                    if (index>0){
                        i=i+1;
                        continue;
                    }
                    for (int j=0;j<colnum;j++){
                        cellData = ExcelUtils.getCellValue(row.getCell(j));
                        map.put(columns[j], cellData.trim());
                    }
                }else{
                    break;
                }
                DealFlow deal = new DealFlow();
                deal.setAccountBody(map.get("客户名称"));
                deal.setCurrency(map.get("币种"));
                String datatime = map.get("交易时间");
                if (map.get("交易时间").length()<6) datatime= "000000";
                String data = map.get("交易日期")+datatime;
                SimpleDateFormat sd = new SimpleDateFormat("yyyyMMddHHssmm");
                Date date = null;
                try {
                    date = sd.parse(data);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                if(date!=null)  deal.setTradeDate(new Timestamp(date.getTime()));
                deal.setAccountBodyNumber(map.get("交易账号"));
                deal.setContentAbstract(map.get("摘要"));
                String st = map.get("借贷方向");
                if ("0".equals(st)){
                    deal.setSign(1);
                }else {
                    deal.setSign(0);
                }
                if (!"".equals(map.get("交易金额"))&&map.get("交易金额")!=null)deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                if (!"".equals(map.get("账户余额"))&&map.get("账户余额")!=null)deal.setSurplusMoney(new BigDecimal(map.get("账户余额")));
                deal.setAccountAddress(map.get("交易网点"));
                deal.setAccountRivalNumber(map.get("对方账号"));
                deal.setAccountRival(map.get("对方户名"));
                deal.setAccountRivalBank(map.get("对方行名"));
                deal.setSerialNumber(map.get("交易流水"));
                deal.setTellerNumber(map.get("交易柜员"));
                deal.setRemarks(map.get("备注"));

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);
            }
        }

        return list;
    }*/

    /**
     * 解析中原银行EXCEL固定模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisZhongYuanBank(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        DealFlow dealFlow=null;
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取标题行
            row = sheet.getRow(3);
            //获取正文的数据列数
            int physicalNumberOfCells = row.getPhysicalNumberOfCells();
            //获取第二行的数据（比较特殊，所以需要特殊单独获取下）
            Row row1 = sheet.getRow(1);
            String cellValue = ExcelUtils.getCellValue(row1.getCell(0));
            String[] split = cellValue.split("\n");

            //开始获取主体的数据
            //获取一共几行
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int i=0;i<physicalNumberOfRows;i++){
                Row row2 = sheet.getRow(i + 4);
                //判断是否是空白行
                if (!ExcelUtils.isBlankLine(row2)) break;
                dealFlow = new DealFlow();
                //交易主体,交易主体账号,币种
                dealFlow.setAccountBody(split[0].split(":")[1]);
                dealFlow.setAccountBodyNumber(split[1].split(":")[1]);
                dealFlow.setCurrency(split[2].split(":")[1]);
                for (int j=0;j<physicalNumberOfCells;j++){
                    Cell cell = row2.getCell(j);
                    String cellValue1 = ExcelUtils.getCellValue(cell);
                    switch (j){
                        case 0:
                            SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");
                            Date date = null;
                            try {
                                date = sd.parse(cellValue1);
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                            if(date!=null)  dealFlow.setTradeDate(new Timestamp(date.getTime()));
                            break;  //交易日期
                        case 1:if (! "0.0".equals(cellValue1)){//收入金额
                            dealFlow.setAccountMoney(new BigDecimal(cellValue1)) ;
                            dealFlow.setSign(0);
                        }else{
                            dealFlow.setSign(1);
                        }break;
                        case 2:if (! "0.0".equals(cellValue1)) dealFlow.setAccountMoney(new BigDecimal(cellValue1)) ;break;//支出金额
                        case 3:if (StringUtils.isNotBlank(cellValue1)) dealFlow.setSurplusMoney(new BigDecimal(cellValue1));break;//余额
                        case 4:dealFlow.setAccountRivalNumber(cellValue1);break;//对方账号
                        case 5:dealFlow.setAccountRival(cellValue1);break;//对方客户名称
                        case 6:dealFlow.setContentAbstract(cellValue1);break;//摘要
                    }
                }
                if (dealFlow.getAccountBodyCard()==null||"".equals(dealFlow.getAccountBodyCard())){
                    dealFlow.setAccountBodyCard(dealFlow.getAccountBodyNumber());
                }
                if (dealFlow.getAccountRivalCard()==null||"".equals(dealFlow.getAccountRivalCard())){
                    dealFlow.setAccountRivalCard(dealFlow.getAccountRivalNumber());
                }

                list.add(dealFlow);
            }

            return list;
        }
        return null;
    }

    /**
     * 解析网银模板银行EXCEL固定模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisWangYin(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String columns1[] = new String[20];
        String columns3[] = new String[20];
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一个表头信息
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            //读取正文
            for (int i = 0; i<rownum; i+=5) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                //第一行读取标题
                row = sheet.getRow(i);
                for (int j=0;j<colnum;j++){
                    columns1[j] = ExcelUtils.getCellValue(row.getCell(j));

                }
                row = sheet.getRow(i+1);
                for (int j=0;j<colnum;j++){
                    cellData = ExcelUtils.getCellValue(row.getCell(j));
                    map.put(columns1[j], cellData.trim());
                }
                //第三行
                row = sheet.getRow(i+2);
                for (int j=0;j<colnum;j++){
                    columns3[j] = ExcelUtils.getCellValue(row.getCell(j));
                }
                //第四行
                row = sheet.getRow(i+3);
                for (int j=0;j<colnum;j++){
                    cellData = ExcelUtils.getCellValue(row.getCell(j));
                    map.put(columns3[j], cellData.trim());
                }

                DealFlow deal = new DealFlow();
                deal.setAccountBody(map.get("付款账户"));
                deal.setCurrency(map.get("交易货币"));
                String data = map.get("交易日期");
                SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd HH:ss:mm");
                Date date = null;
                try {
                    date = sd.parse(data);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                if(date!=null)  deal.setTradeDate(new Timestamp(date.getTime()));
                deal.setAccountBodyNumber(map.get("付款帐号"));
                deal.setAccountBodyBank(map.get("付款网银机构名称"));
                String st = map.get("渠道类型");
                if ("0".equals(st)){
                    deal.setSign(1);
                }else {
                    deal.setSign(0);
                }
                if (!"".equals(map.get("交易金额"))&&map.get("交易金额")!=null)deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                //if (!"".equals(map.get("账户余额"))&&map.get("账户余额")!=null)deal.setSurplusMoney(new BigDecimal(map.get("账户余额")));
                deal.setAccountRivalNumber(map.get("收款帐号"));
                deal.setAccountRival(map.get("收款账户"));
                deal.setAccountRivalBank(map.get("收款账户银行名称"));
                deal.setSerialNumber(map.get("交易流水号"));
                deal.setInstitutionNumber(map.get("收款账户网银机构号"));
                deal.setTradingOrganizationNumber(map.get("付款网银机构号"));

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);
            }
            return list;
        }

        return null;
    }

    /**
     * 解析支付宝EXCEL固定模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisAlipay(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String columns[] = new String[256];
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for(int i=0;i<colnum;i++){
                columns[i] = ExcelUtils.getCellValue(row.getCell(i));
            }
            for (int i = 1; i<rownum; i++) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(ExcelUtils.isBlankLine(row)){
                    for (int j=0;j<colnum;j++){
                        cellData = ExcelUtils.getCellValue(row.getCell(j));
                        if ("-".equals(cellData))cellData = "";
                        map.put(columns[j], cellData);
                    }
                }else{
                    break;
                }
                DealFlow deal = new DealFlow();
                String s = map.get("交易主体的出入账标识");
                if ("入".equals(s)){//根据借贷标识判断交易主体是收款人还是付款人
                    deal.setSign(0);
                    deal.setAccountRival(map.get("付款支付帐号"));
                    deal.setAccountRivalNumber(map.get("付款银行卡号"));
                    deal.setAccountRivalCard(map.get("付款支付帐号"));
                    deal.setAccountRivalBank(map.get("付款银行卡银行名称"));
                    deal.setAccountBody(map.get("收款支付帐号"));
                    deal.setAccountBodyNumber(map.get("收款银行卡号"));
                    deal.setAccountBodyCard(map.get("收款支付帐号"));
                    deal.setAccountBodyBank(map.get("收款银行卡银行名称"));
                }else if ("出".equals(s)){
                    deal.setSign(1);
                    deal.setAccountBody(map.get("付款支付帐号"));
                    deal.setAccountBodyCard(map.get("付款支付帐号"));
                    deal.setAccountBodyNumber(map.get("付款银行卡号"));
                    deal.setAccountBodyBank(map.get("付款银行卡银行名称"));

                    deal.setAccountRival(map.get("收款支付帐号"));
                    deal.setAccountRivalCard(map.get("收款支付帐号"));
                    deal.setAccountRivalNumber(map.get("收款银行卡号"));
                    deal.setAccountRivalBank(map.get("收款银行卡银行名称"));

                }
                deal.setSerialNumber(map.get("交易流水号"));
                deal.setAccountMode(map.get("交易类型"));
                if (!"".equals(map.get("交易金额"))&&map.get("交易金额")!=null)deal.setAccountMoney(new BigDecimal(map.get("交易金额")));
                if (!"".equals(map.get("交易余额"))&&map.get("交易余额")!=null)deal.setSurplusMoney(new BigDecimal(map.get("交易余额")));
                deal.setCurrency(map.get("币种"));
                String data =map.get("交易时间");
                SimpleDateFormat sd = new SimpleDateFormat("yyyyMMddHHssmm");
                Date date = null;
                try {
                    date = sd.parse(data);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                if(date!=null)  deal.setTradeDate(new Timestamp(date.getTime()));
                deal.setIpAddress(map.get("交易支付设备IP"));
                deal.setMacAddress(map.get("MAC地址"));
                deal.setRemarks(map.get("备注"));

                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }

                list.add(deal);
            }
        }

        return list;
    }

    /**
     * 解析X张三等24个主体人身份信息模板
     * @param wb
     * @return
     */
    public List<DealFlow> analysisZhangSan(Workbook wb,String fileName) {
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        String cellData = null;
        String columns[] = new String[100];
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<DealFlow>();
            //获取第二个sheet
            sheet = wb.getSheetAt(1);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一个表头信息
            row = sheet.getRow(2);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i=0;i<colnum;i++){
                columns[i] = ExcelUtils.getCellValue(row.getCell(i)).trim();
            }

            //读取正文
            for (int i = 2; i<rownum; i++) {
                row = sheet.getRow(i);
                if (ExcelUtils.isBlankLine(row)&&"序号".equals(ExcelUtils.getCellValue(row.getCell(0)).trim())){
                    String name = ExcelUtils.getCellValue(sheet.getRow(i-1).getCell(0)).trim();
                    name = name.substring(name.indexOf(")")+1);
                    for (i=i+1;i<rownum;i++){
                        row = sheet.getRow(i);
                        if (!ExcelUtils.isBlankLine(row))break;
                        Map<String,String> map = new HashMap<>();
                        for(int j=0;j<colnum;j++){
                            map.put(columns[j],ExcelUtils.getCellValue(row.getCell(j)).trim());
                        }

                        DealFlow deal = new DealFlow();
                        if (name.equals(map.get("付款方名称"))){
                            deal.setSign(1);
                            deal.setAccountBody(name);
                            deal.setAccountBodyNumber(map.get("付款方账号").replace("'",""));
                            deal.setIdNumber(map.get("付款方证件号码").replaceAll("'",""));
                            deal.setAccountBodyBank(map.get("付款方银行").replaceAll("'",""));
                            deal.setAccountRival(map.get("收款方名称"));
                            deal.setAccountRivalNumber(map.get("收款方账号").replaceAll("'",""));
                            deal.setAccountRivalBank(map.get("收款方银行").replaceAll("'",""));
                        }else {
                            deal.setSign(0);
                            deal.setAccountBody(name);
                            deal.setAccountBodyNumber(map.get("收款方账号").replace("'",""));
                            deal.setIdNumber(map.get("收款方证件号码").replaceAll("'",""));
                            deal.setAccountBodyBank(map.get("收款方银行").replaceAll("'",""));
                            deal.setAccountRival(map.get("付款方名称"));
                            deal.setAccountRivalNumber(map.get("付款方账号").replaceAll("'",""));
                            deal.setAccountRivalBank(map.get("付款方银行").replaceAll("'",""));
                        }
                        String s = map.get("交易时间").replaceAll("'","");
                        Date date = DateUtils.getDate(s);
                        if (date!=null)deal.setTradeDate(new Timestamp(date.getTime()));
                        deal.setAccountMode(map.get("交易方式"));
                        deal.setCurrency(map.get("货币名称"));
                        deal.setAccountMoney(new BigDecimal(map.get("原币金额")));
                        deal.setSerialNumber(map.get("交易记录ID").replaceAll("'",""));
                        deal.setAccountAddress(map.get("交易发生地"));
                        deal.setTradingOrganizationNumber(map.get("报告机构"));
                        deal.setContentAbstract(map.get("用途"));
                        if (name.contains("公司")){
                            deal.setRemarks(fileName+"-"+"主体公司"+name);
                        }else {
                            deal.setRemarks(fileName+"-"+"主体人"+name);
                        }


                        if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                            deal.setAccountBodyCard(deal.getAccountBodyNumber());
                        }
                        if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                            deal.setAccountRivalCard(deal.getAccountRivalNumber());
                        }

                        list.add(deal);
                    }
                }

            }
            return list;
        }

        return null;
    }

}
